package fr.miage.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import fr.miage.connexion.ConnexionBD;
import fr.miage.model.Invitation;

public class InvitationDAO {
	private Logger logger = Logger.getRootLogger();
	
	public Invitation getById(String idUser, String idEvent) {		
		ResultSet res = null;
		String query = "select * from invitation WHERE idUser="+idUser+" AND idEvent="+idEvent;
		Statement st = null;
		Invitation invit = null;
		try {
			st = ConnexionBD.getInstance().getConn().createStatement();
			res = st.executeQuery(query);
			if(res.next()){
				invit = getInvitation(res);
			}else{
				return null;
			}
		} catch (SQLException ex) {
			logger.error(ex);
		} finally {
			try {
				if (res != null) {
					res.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		
		return invit;
	}

	private Invitation getInvitation(ResultSet res) {
		Invitation invit = new Invitation();
		try {
			invit.setIdEvent(res.getLong("idEvent"));
			invit.setIdUser(res.getLong("idUser"));
			invit.setResponse(res.getString("response"));
			invit.setDate(res.getString("date"));
			invit.setRole(res.getString("role"));
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return invit;
	}

	public List<Invitation> getAllInvitByUser(String idUser) {
		ResultSet res = null;
		String query = "select * from invitation WHERE idUser="+idUser;
		Statement st = null;
		List<Invitation> listeInvit = null;
		try {
			st = ConnexionBD.getInstance().getConn().createStatement();
			res = st.executeQuery(query);
			listeInvit = new ArrayList<Invitation>();
			while(res.next()){
				Invitation invit = getInvitation(res);
				listeInvit.add(invit);
			}
		} catch (SQLException ex) {
			logger.error(ex);
		} finally {
			try {
				if (res != null) {
					res.close();
				}
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				logger.error(e);
			}
		}
		
		return listeInvit;
	}

	public int addInvitation(Invitation invit) {
		int res = 0;
		Date date = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		String query = "insert into invitation values ("+invit.getIdEvent()+", "+invit.getIdUser()+", '"+invit.getResponse()+"', '"+invit.getRole()+"', '"+format.format(date)+"')";
		Statement st = null;
		try {
			st = ConnexionBD.getInstance().getConn().createStatement();
			res = st.executeUpdate(query);
			return res;
		} catch(MySQLIntegrityConstraintViolationException e){
			logger.error(e);
			e.printStackTrace();
			return -1;
		} catch (SQLException ex) {
			logger.error(ex);
			ex.printStackTrace();
			return res;
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
	}

	public int updateInvitation(Invitation invit) {
		int res = 0;
		String query = "UPDATE invitation SET response="+invit.getResponse()+", role='"+invit.getRole()+"' WHERE idEvent="+invit.getIdEvent()+" AND idUser="+invit.getIdUser();
		Statement st = null;
		try {
			st = ConnexionBD.getInstance().getConn().createStatement();
			res = st.executeUpdate(query);
			return res;
		} catch (SQLException ex) {
			logger.error(ex);
			ex.printStackTrace();
			return -1;
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
	}
}
